/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Mapper;

import Bean.AccountBean;
import Bean.CartBean;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.text.*;

/**
 *
 * @author Quang Thao
 */
public class CartMapper extends DBMapper {

    public CartMapper() throws SQLException, Exception {
        super();
    }

    public int Add(Bean.CartBean cart) throws Exception {
        DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
        int kq = 0;
        PreparedStatement stmt = getConnection().prepareStatement("Insert Into Cart Values('"
                + cart.getID() + "','" + cart.getAccount_ID().getID() + "'," + cart.getSumCost() + ",'"
                + df.format(cart.getDateCreate()) + "','" + df.format(cart.getDateArrive())
                + "','" + cart.getState() + "','" + cart.getType() + "')");
        kq = stmt.executeUpdate();
        if (kq == 1) {
            for (int i = 0; i < cart.getListBook().size(); i++) {
                Bean.BookBean book = (Bean.BookBean) cart.getListBook().get(i);
                stmt = getConnection().prepareStatement("Insert Into CartDetail Values('" + cart.getID()
                        + "','" + book.getISBN() + "'," + book.getQuantity() + "," + book.getCost() + ")");
                kq = stmt.executeUpdate();
            }
        }
        stmt.close();
        return kq;
    }

    public String GetNewID() throws Exception {
        Date now = new Date();
        DateFormat df = new SimpleDateFormat("ddMM");
        PreparedStatement stmt = getConnection().prepareStatement("Select * From Cart Where ID LIKE '" + df.format(now) + "%'");
        ResultSet rs = stmt.executeQuery();
        int i = 1;
        if (rs != null) {
            while (rs.next()) {
                i++;
            }
        }
        String newID = df.format(now) + String.format("%03d", i);
        stmt.close();
        return newID;
    }

    public List GetListByType(String State) throws Exception {
        List list = new ArrayList<Bean.CartBean>();
        PreparedStatement stmt = getConnection().prepareStatement("SELECT * FROM Cart c, Account a WHERE c.Account_ID = a.ID and c.State='" + State + "'");
        ResultSet rs = stmt.executeQuery();
        if (rs != null) {
            while (rs.next()) {
                Bean.CartBean cart = new Bean.CartBean();
                Bean.AccountBean acc = new Bean.AccountBean();
                cart.setID(rs.getString(1));
                acc.setID(rs.getString(8));
                acc.setName(rs.getString(10));
                acc.setEmail(rs.getString(11));
                acc.setPhone(rs.getString(12));
                acc.setType(rs.getString(14));
                cart.setAccount_ID(acc);
                cart.setState(rs.getString(6));
                cart.setType(rs.getString(7));
                cart.setCost(rs.getDouble(3));
                list.add(cart);
            }
        }
        stmt.close();
        return list;
    }

    public List GetAll() throws Exception {
        List list = new ArrayList<Bean.CartBean>();
        PreparedStatement stmt = getConnection().prepareStatement("SELECT * FROM Cart c, Account a WHERE c.Account_ID = a.ID");
        ResultSet rs = stmt.executeQuery();
        if (rs != null) {
            while (rs.next()) {
                Bean.CartBean cart = new Bean.CartBean();
                Bean.AccountBean acc = new Bean.AccountBean();
                cart.setID(rs.getString(1));
                acc.setID(rs.getString(8));
                acc.setName(rs.getString(10));
                acc.setEmail(rs.getString(11));
                acc.setPhone(rs.getString(12));
                acc.setType(rs.getString(14));
                cart.setAccount_ID(acc);
                cart.setState(rs.getString(6));
                cart.setType(rs.getString(7));
                cart.setCost(rs.getDouble(3));
                list.add(cart);
            }
        }
        stmt.close();
        return list;
    }

    public int Delete(CartBean cart) throws Exception {
        PreparedStatement stmt = getConnection().prepareStatement("Delete From Cart Where ID = '" + cart.getID() + "'");
        stmt.executeUpdate();
        stmt = getConnection().prepareStatement("Delete From CartDetail Where Cart_ID = '" + cart.getID() + "'");
        int found = stmt.executeUpdate();
        stmt.close();
        return found;
    }

    public int UpdateSell(Bean.CartBean cart) throws Exception {
        PreparedStatement stmt = getConnection().prepareStatement("Update Cart Set State='Already'"
                + " WHERE ID = '" + cart.getID() + "'");
        int kq = stmt.executeUpdate();
        stmt.close();
        return kq;
    }
     public List GetCartDetail(String CartID) throws Exception {
        List list = new ArrayList<Bean.CartBean>();
        PreparedStatement stmt = getConnection().prepareStatement("SELECT * FROM Book b, Publisher p, CartDetail c "
                + "WHERE c.ISBN = b.ISBN and b.Publisher = p.ID and c.Cart_ID = '"+ CartID +"'");
        ResultSet rs = stmt.executeQuery();
        if (rs != null) {
            while (rs.next()) {
                List cartDetail = new ArrayList();
                cartDetail.add("<img src='../../Content/Images/Book/" +rs.getString(10) +"'/>");
                cartDetail.add(rs.getString(2));
                cartDetail.add(rs.getString(9));
                cartDetail.add(rs.getString(12));
                cartDetail.add(rs.getString(7));
                cartDetail.add(rs.getString(15));
                cartDetail.add(rs.getString(16));
                list.add(cartDetail);
            }
        }
        stmt.close();
        return list;
    }
}
